<?php
namespace frontend\controllers;

use Yii;
use yii\web\Controller;
use yii\web\Session;
use PDO;
class FieldsController extends Controller{

	/*
	 * 禁用yii样式
	 */
	public $layout = false;
	public $enableCsrfValidation = false;


	public function pdo()
	{
		$k = Yii::$app->session->get('link_k');
		$dbname = Yii::$app->session->get('dbname');
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        $one_dbinfo = $dbinfos_arr[$k];
        $pdo = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];dbname=$dbname;charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");
		$pdo->query("set names utf8");
        return $pdo;
	}
	/*
	 * 展示表字段
	 */
	public function actionField_view(){
		$tables = Yii::$app->session->get('table');	
		$pdo = $this->pdo();					
		$sql = "select * from $tables";
		$data = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
		$sqls = "desc $tables";
        $list = $pdo->query($sqls)->fetchAll(PDO::FETCH_ASSOC);
        // print_r()
		return $this->render('field_view',['data'=>$data,'list'=>$list]);
	}

	/*
	 * 展示表结构
	 */
	public function actionField_structure(){
		$table = Yii::$app->session->get('table');
		$pdo = $this->pdo();
		$arr=$pdo->query("show columns from $table")->fetchAll(PDO::FETCH_ASSOC);
		$key=array_keys($arr[0]);
		return $this->render('field_structure',['key'=>$key,'arr'=>$arr,'table'=>$table]);
	}
	/*
	 *添加字段
	 */
	public function actionAdd(){
		$table = Yii::$app->session->get('table');
		$pdo = $this->pdo();
		
		//接字段相关值
		$name=$_POST['name'];
		$dafa=$_POST['dafa'];
		$type=$_POST['type'];
		$nul=$_POST['nul'];
		$len=$_POST['len'];
		//echo $len;die;
		//字符类型及大小处理
		$str=$type."(".$len.")	";
		//判断可否为空
		if($nul==0){//值为0 可谓空
			$null="	";
		}else{
			$null="	not null	";
		}
		//默认值处理
		if($dafa==""){
			$dafult="	";
		}else{
			$dafult="	default	".$dafa;
		}
		//执行sql语句
		$sql="alter table	`".$table."`	Add	`".$name."`	".$str.$null.$dafult;
		//echo $sql;die;
		$arr=$pdo->exec($sql);
		//print_r($pdo->errorInfo());
		if($arr){
			echo "添加成功！";
		}else{
			echo "添加失败，请检查添加是否符合规范";
		}
	}
	/**
	 *删除字段 
	 */
	public function actionDel(){
		$table = Yii::$app->session->get('table');
		$pdo = $this->pdo();
		//接字段值
		$fid=$_GET['fid'];
		$sql="alter table	`".$table."`	drop	`".$fid."`";
		//echo $sql;die;
		$arr=$pdo->exec($sql);
		if($arr){
			echo "删除成功！";
		}else{
			echo "删除失败！";
		}
	}
	/**
	 * /修改表字段
	 */
	public function actionUpdate(){
		//连接数据库 获取表名
		$table = Yii::$app->session->get('table');
		$pdo = $this->pdo();
		//接值
		//新值
		$news=$_GET['news'];
		//旧值
		$fid=$_GET['fid'];
		//判断字段属性
		$kid=$_GET['kid'];
		if($kid=="Type"){
			$len= strpos($news,"(");
			$str=substr($news,0,$len);
			$num=substr($news,$len*1+1,-1);
			$key=$str."(".$num.")";
			$sql="alter table	`".$table."`	modify	`".$fid."`	".$key;
			$arr=$pdo->exec($sql);
			if($arr){
				echo 1;
			}
		}elseif($kid=="Field"){
			$cah=$_GET['cha'];
			$len= strpos($cah,"(");
			$str=substr($cah,0,$len);
			$num=substr($cah,$len*1+1,-1);
			$key=$str."(".$num.")";

			$sql="alter table	`".$table."`	change	`".$fid."`	`".$news."`	".$key;
			$arr=$pdo->exec($sql);
			if($arr){
				echo 1;
			}	
		}elseif($kid=="Default"){
			$key=$kid;
		}

	}
	/**
	 * 添加索引
	 */
	public function actionAdd_index(){
		$table = Yii::$app->session->get('table');
		$pdo = $this->pdo();
		$filed=$_GET['filed'];
		$type=$_GET['type'];
		$name=$_GET['name'];
		$sql="alter	table $table add $type $name($filed)";
		$arr=$pdo->exec($sql);
		return 1;
	}
	/**
	 * 删除索引
	 */
	public function actionDel_index(){
		$table = Yii::$app->session->get('table');
		$pdo = $this->pdo();
		//接字段值
		$fid=$_GET['fid'];
		$inde=$_GET['inde'];
		$sql1="show index from	`".$table."`";
		$data=$pdo->query($sql1)->fetchAll(PDO::FETCH_ASSOC);
		foreach($data as $k=>$v){
			if($v['Column_name']==$fid){
				$sql="alter table	`".$table."`	drop	index	".$v['Key_name'];
				//echo $sql;die;
				$arr=$pdo->exec($sql);
				if($arr){
					echo "删除失败！";
				}else{
					echo "删除成功！";
				}
			}

		}
		
	}
	public function actionAjaxfield(){

		$k = Yii::$app->request->post('k');
		$db = Yii::$app->request->post('db');
		$table_name = Yii::$app->request->post('table_name');
		// $k = '0';
		// $db = 'xutao';
		// $table_name = 'xutao';

		$use_sql = "use `$db`";
		$sql = "select * from `$table_name`";
		$desc_sql = "desc `$table_name`";

		$one_dbinfo = $this->pdo($k);
		$pdo = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];dbname=$db;charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");

		// $pdo->execute($use_sql);

		// $sths = $pdo->prepare($sql);
        // $sths->execute();
        $arr = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $field = $pdo->query($desc_sql)->fetchAll(PDO::FETCH_ASSOC);

        $sthst = $pdo->prepare($desc_sql);
        $sthst->execute();
        $field = $sthst->fetchAll();

		foreach ($field as $key => $value) {
			$fields[$key] = $value['Field'];
			$data[2][$key]['name'] = $value['Field'];
			$data[2][$key]['index'] = $value['Field'];
			$data[2][$key]['editable'] = true;
			$data[2][$key]['width'] = 100;
		}
		$data[0] = $arr;
		$data[1] = $fields;
		$data[3] = $table_name.' 表';
		// echo "<pre>";
		// print_r($data);die;
		echo  json_encode($data);die;
	}
}